<!DOCTYPE html>
<html lang="en">
  <head>
    <title>
        MySQL-2-基础 - rulerLwx Blog
      </title>
        <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport"
      content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
    <meta name="renderer" content="webkit">
    <meta http-equiv="Cache-Control" content="no-transform" />
    <meta http-equiv="Cache-Control" content="no-siteapp" />
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="format-detection" content="telephone=no,email=no,adress=no">
    
    <meta name="theme-color" content="#000000" />
    
    <meta http-equiv="window-target" content="_top" />
    
    
    <meta name="description" content="存储过程 学习资料：https://www.bilibili.com/video/av22645209/?p=4 预备知识：MySQL中的变量 局" />
    <meta name="generator" content="Hugo 0.73.0 with theme pure" />
    <title>MySQL-2-基础 - rulerLwx Blog</title>
    
    
    <link rel="stylesheet" href="https://rulerLwx.gitee.io/css/style.min.c4bc7071f132c964c2116bca53b392933f377e5ca7b7051ed245187c621a2d3e.css">
    
    <link rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/9.15.10/styles/github.min.css" async>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.css" async>
    <meta property="og:title" content="MySQL-2-基础" />
<meta property="og:description" content="存储过程 学习资料：https://www.bilibili.com/video/av22645209/?p=4 预备知识：MySQL中的变量 局" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://rulerLwx.gitee.io/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/" />
<meta property="article:published_time" content="2020-07-01T18:58:47+08:00" />
<meta property="article:modified_time" content="2020-07-01T18:58:47+08:00" />
<meta itemprop="name" content="MySQL-2-基础">
<meta itemprop="description" content="存储过程 学习资料：https://www.bilibili.com/video/av22645209/?p=4 预备知识：MySQL中的变量 局">
<meta itemprop="datePublished" content="2020-07-01T18:58:47&#43;08:00" />
<meta itemprop="dateModified" content="2020-07-01T18:58:47&#43;08:00" />
<meta itemprop="wordCount" content="3035">



<meta itemprop="keywords" content="" /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="MySQL-2-基础"/>
<meta name="twitter:description" content="存储过程 学习资料：https://www.bilibili.com/video/av22645209/?p=4 预备知识：MySQL中的变量 局"/>

    <!--[if lte IE 9]>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
      <![endif]-->

    <!--[if lt IE 9]>
        <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
      <![endif]-->
  </head>

  
  

  <body class="main-center theme-black" itemscope itemtype="http://schema.org/WebPage"><header class="header" itemscope itemtype="http://schema.org/WPHeader">
    <div class="slimContent">
      <div class="navbar-header">
        <div class="profile-block text-center">
          <a id="avatar" href="https://gitee.com/rulerLwx" target="_blank">
            <img class="img-circle img-rotate" src="https://rulerLwx.gitee.io/avatar.png" width="200" height="200">
          </a>
          <h2 id="name" class="hidden-xs hidden-sm">rulerLwx</h2>
          <h3 id="title" class="hidden-xs hidden-sm hidden-md">thinking...</h3>
          <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i>Guangzhou, China</small>
        </div><div class="search" id="search-form-wrap">
    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="Search" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i
                        class="icon icon-search"></i></button>
            </span>
        </div>
        <div class="ins-search">
            <div class="ins-search-mask"></div>
            <div class="ins-search-container">
                <div class="ins-input-wrapper">
                    <input type="text" class="ins-search-input" placeholder="Type something..."
                        x-webkit-speech />
                    <button type="button" class="close ins-close ins-selectable" data-dismiss="modal"
                        aria-label="Close"><span aria-hidden="true">×</span></button>
                </div>
                <div class="ins-section-wrapper">
                    <div class="ins-section-container"></div>
                </div>
            </div>
        </div>
    </form>
</div>
        <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
          <span class="sr-only">Toggle navigation</span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
        </button>
      </div>
      <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
        <ul class="nav navbar-nav main-nav">
            <li class="menu-item menu-item-home">
                <a href="/">
                    <i class="icon icon-home-fill"></i>
                  <span class="menu-title">Home</span>
                </a>
            </li>
            <li class="menu-item menu-item-archives">
                <a href="/posts/">
                    <i class="icon icon-archives-fill"></i>
                  <span class="menu-title">Archives</span>
                </a>
            </li>
            <li class="menu-item menu-item-categories">
                <a href="/categories/">
                    <i class="icon icon-folder"></i>
                  <span class="menu-title">Categories</span>
                </a>
            </li>
            <li class="menu-item menu-item-tags">
                <a href="/tags/">
                    <i class="icon icon-tags"></i>
                  <span class="menu-title">Tags</span>
                </a>
            </li>
            <li class="menu-item menu-item-about">
                <a href="/about/">
                    <i class="icon icon-cup-fill"></i>
                  <span class="menu-title">About</span>
                </a>
            </li>
        </ul>
      </nav>
    </div>
  </header>

<aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">Board</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">enjoy~
            </div>
        </div>
    </div>
</div>

      <div class="widget">
    <h3 class="widget-title"> Categories</h3>
    <div class="widget-body">
        <ul class="category-list">
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-framework/" class="category-list-link">java-framework</a><span class="category-list-count">38</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-front-end/" class="category-list-link">java-front-end</a><span class="category-list-count">11</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-se/" class="category-list-link">java-se</a><span class="category-list-count">21</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-senior/" class="category-list-link">java-senior</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/linux/" class="category-list-link">linux</a><span class="category-list-count">13</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/tools/" class="category-list-link">tools</a><span class="category-list-count">1</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%8A%80%E6%9C%AF%E6%9D%82%E7%83%A9/" class="category-list-link">技术杂烩</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" class="category-list-link">数据库</a><span class="category-list-count">15</span></li>
        </ul>
    </div>
</div>
      <div class="widget">
    <h3 class="widget-title"> Tags</h3>
    <div class="widget-body">
        <ul class="tag-list">
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/jvm/" class="tag-list-link">jvm</a><span
                    class="tag-list-count">1</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" class="tag-list-link">多线程</a><span
                    class="tag-list-count">2</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/" class="tag-list-link">网络编程</a><span
                    class="tag-list-count">3</span></li>
            
        </ul>

    </div>
</div>
      
<div class="widget">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget-body">
        <ul class="recent-post-list list-unstyled no-thumbnail">
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%87%AA%E5%B7%B1%E5%8A%A8%E6%89%8B%E4%B8%80%E6%89%B9%E9%87%8F%E7%A7%BB%E5%8A%A8%E9%87%8D%E5%91%BD%E5%90%8D%E6%96%87%E4%BB%B6/" class="title">自己动手（一）——批量移动、重命名文件</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-27 17:50:02 &#43;0800 CST" itemprop="datePublished">2020-07-27</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E5%85%B3%E4%BA%8Einteger%E7%9A%84-128~127%E7%BC%93%E5%AD%98/" class="title">关于Integer的 -128~127缓存</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-11 16:56:21 &#43;0800 CST" itemprop="datePublished">2020-07-11</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%B7%A8%E5%9F%9F%E9%97%AE%E9%A2%98/" class="title">跨域问题</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-08 22:41:12 &#43;0800 CST" itemprop="datePublished">2020-07-08</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E4%B8%AA%E4%BA%BA%E5%8D%9A%E5%AE%A2%E6%90%AD%E5%BB%BA/" class="title">个人博客搭建</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-05 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-05</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/centos/" class="title">CentOS-</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-01 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-01</time>
                    </p>
                </div>
            </li>
        </ul>
    </div>
</div>
  </div>
</aside>

    
    
<aside class="sidebar sidebar-toc collapse" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    <h4 class="toc-title">Catalogue</h4>
    <nav id="toc" class="js-toc toc">

    </nav>
  </div>
</aside>
<main class="main" role="main"><div class="content">
  <article id="-" class="article article-type-" itemscope
    itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      <h1 itemprop="name">
  <a
    class="article-title"
    href="/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/"
    >MySQL-2-基础</a
  >
</h1>

      <div class="article-meta">
        
<span class="article-date">
  <i class="icon icon-calendar-check"></i>&nbsp;
<a href="https://rulerLwx.gitee.io/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/" class="article-date">
  <time datetime="2020-07-01 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-01</time>
</a>
</span>
<span class="article-category">
  <i class="icon icon-folder"></i>&nbsp;
  <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"> 数据库 </a>
</span>

        <span class="post-comment"><i class="icon icon-comment"></i>&nbsp;<a href="/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/#comments"
            class="article-comment-link">Comments</a></span>
		<span class="post-wordcount hidden-xs" itemprop="wordCount">Word Count: 3035words</span>
		<span class="post-readcount hidden-xs" itemprop="timeRequired">Read Count: 7minutes </span>
      </div>
    </div>
    <div class="article-entry marked-body js-toc-content" itemprop="articleBody">
      <h1 id="存储过程">存储过程</h1>
<p>学习资料：https://www.bilibili.com/video/av22645209/?p=4</p>
<h2 id="预备知识mysql中的变量">预备知识：MySQL中的变量</h2>
<ul>
<li>局部变量：declare</li>
<li>用户变量：set/select</li>
<li>会话变量</li>
<li>全局变量</li>
</ul>
<p>参考：https://www.cnblogs.com/EasonJim/p/7966918.html ， <a href="https://www.cnblogs.com/gavin110-lgy/p/5772577.html">https://www.cnblogs.com/gavin110-lgy/p/5772577.html</a></p>
<p>查看定义的变量：<code>select 变量名,变量名,..</code> 或 <code>select @变量名,@变量名,..</code></p>
<h2 id="创建存储过程">创建存储过程</h2>
<p>学习资料：《MySQL数据库入门》传智播客</p>
<p>储存过程就是一条或多条sql语句的集合。</p>
<p>如果使用命令行创建存储过程，可以使用 delimiter 定义结束符，如<code>delimiter //</code>，注意：delimiter与结束符之间要有一个空格，否则无效</p>
<p>1）语法定义</p>
<pre><code class="language-sql">CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT  'string'
  | LANGUAGE  SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS  SQL | NO  SQL | READS  SQL  DATA | MODIFIES  SQL  DATA }
  | SQL  SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement
</code></pre>
<p>说明：</p>
<ul>
<li>LANGUAGE SQL ：说明 routine_body 部分是由 sql 组成的，sql 是 LANGUAGE 的唯一值</li>
<li>[NOT] DETERMINISTIC ：指明存储过程执行结果是否确定，默认为 NOT DETERMINISTIC</li>
<li>{ CONTAINS SQL | NO  SQL | READS SQL DATA | MODIFIES SQL DATA } ：默认 CONTAINS SQL</li>
<li>SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行，默认 DEFINER</li>
</ul>
<hr>
<p>学习资料：https://www.bilibili.com/video/av21400736/?p=156</p>
<pre><code class="language-sql">CREATE PROCEDURE `存储过程名` (参数列表)
BEGIN

存储过程体（一组合法的SQL语句）

END
</code></pre>
<p>参数列表包含三个部分：参数模式 参数名 参数类型，例：<code>IN stuname VARCHAR(20)</code></p>
<p>参数模式：</p>
<ul>
<li>IN ：输入参数，调用方需要传入参数</li>
<li>OUT ：输出参数，该参数可以作为返回值</li>
<li>INOUT ：输入输出参数，该参数即可输入也可输出</li>
</ul>
<p>2）变量的定义、赋值</p>
<p>变量定义、赋值在 begin&hellip;end 之间</p>
<p>语法：DECLARE var_name [, var_name] &hellip; data_type [DEFAULT value]</p>
<p>为变量赋值，语法：set var_name = expr[,var_name = expr]&hellip;</p>
<p>官方文档：https://dev.mysql.com/doc/refman/5.5/en/stored-program-variables.html</p>
<p>示例：</p>
<pre><code class="language-sql">declare var1,var2,var3 int;
set var1 = 10,var2 = 20;
set var3 = var1 + var2;

declare s_grade float;
declare s_gender char(2);
select grade,gender into s_grade,s_gender from student where name = 'rose';
</code></pre>
<h2 id="定义条件和处理程序">定义条件和处理程序</h2>
<p><strong>定义条件</strong>是事先定义程序执行过程中遇到的问题，<strong>处理程序</strong>定义了在遇到这些问题时应采取的处理方式，并且保证存储过程在遇到警告或错误时能继续执行。</p>
<p>1）定义条件</p>
<p>TODO&hellip;</p>
<h2 id="示例">示例</h2>
<p>学习资料：https://www.bilibili.com/video/av21400736/?p=159</p>
<h3 id="out参数的存储过程">OUT参数的存储过程</h3>
<p>1）一个输出参数：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709022603.png" alt=""></p>
<p><code>SET @bname</code> 也可以省略不写</p>
<p>2）两个输出参数：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709022630.png" alt=""></p>
<p>3）自我总结：输出参数是存放在自定义的变量中</p>
<h3 id="inout参数的存储过程">INOUT参数的存储过程</h3>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023227.png" alt=""></p>
<p>总结：调用时传入的是两个自定义的变量，这些变量既用于传入值也用于接收值。</p>
<p><code>select @m,@n;</code>是取出存储过程的返回值。</p>
<hr>
<p>其它示例：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023245.png" alt=""></p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023300.png" alt=""></p>
<h2 id="调用存储过程">调用存储过程</h2>
<pre><code class="language-sql">CALL sp_name([parameter[,...]]);
</code></pre>
<p>入参有时是常量，有时是用户变量（当储存过程有输出参数时）</p>
<h1 id="函数">函数</h1>
<p>存储过程与函数区别：</p>
<ul>
<li>存储过程：可以有0个或多个返回，适合批量插入、批量更新</li>
<li>函数：有且仅有1个返回，适合对数据处理后返回一个结果</li>
</ul>
<h2 id="创建函数">创建函数</h2>
<p>1）语法</p>
<pre><code class="language-sql">CREATE FUNCTION `函数名` (参数列表) RETURNS 返回类型
BEGIN

RETURN xxx;
END
</code></pre>
<p>2）示例</p>
<p>无参有返回：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023324.png" alt=""></p>
<p>有参有返回：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023337.png" alt=""></p>
<h2 id="调用函数">调用函数</h2>
<pre><code class="language-sql">select 函数名（参数列表）;
</code></pre>
<h1 id="流程控制">流程控制</h1>
<p>流程控制有三种：</p>
<ul>
<li>顺序结构</li>
<li>分支结构</li>
<li>循环结构</li>
</ul>
<h2 id="分支结构">分支结构</h2>
<h3 id="if">IF</h3>
<p>1）if()函数</p>
<p>if(expr,v1,v2)，如果 expr 表达式为 true，返回 v1，否则返回 v2</p>
<p>2）条件控制</p>
<p>语法：</p>
<pre><code class="language-sql">IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END  IF
</code></pre>
<p>使用位置：只能应用在 BEGIN AND 中</p>
<p>示例：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023409.png" alt=""></p>
<h3 id="case">CASE</h3>
<p>情况1：类似于java中的switch语句，一般用于实现等值判断</p>
<pre><code class="language-sql">CASE 变量|表达式|字段 
WHEN 要判断的值1 THEN 返回的值1或语句1;
WHEN 要判断的值2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;
</code></pre>
<p>情况2：类似于java中的多重IF语句，一般用于实现区间判断</p>
<pre><code class="language-sql">CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;
</code></pre>
<ul>
<li>CASE 作为表达式，嵌套在其它语句中使用，可以放在任何地方：BEGIN AND 或 BEGIN AND 外面</li>
<li>CASE 作为独立语句使用，只能放在 BEGIN AND 中</li>
<li>如果 WHEN 中条件成立，则执行 THEN 中的语句，并结束 CASE ;如果者不满足，则执行 ELSE 中的语句</li>
<li>ELSE 可以省略，如果省略了，并且所有 WHEN 都不满足，则返回 NULL</li>
</ul>
<p>示例：</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023426.png" alt=""></p>
<h2 id="循环结构">循环结构</h2>
<p>分类：</p>
<ul>
<li>WHILE</li>
<li>LOOP</li>
<li>REPEAT</li>
</ul>
<p>循环控制：</p>
<ul>
<li>iterate，类似于 continue，结束本次循环，继续下一次循环。语法：ITERATE lable</li>
<li>leave，类似于 break ，跳出，结束当前所有循环。语法：LEAVE lable</li>
</ul>
<h3 id="while">WHILE</h3>
<p>语法：</p>
<pre><code class="language-sql">[begin_label:] WHILE search_condition DO
    statement_list
END  WHILE [end_label]
</code></pre>
<p>1）没有添加循环控制语句</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023449.png" alt=""></p>
<p>2）添加 leave 语句</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023502.png" alt=""></p>
<p>3）添加 iterate 语句</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023518.png" alt=""></p>
<p>4）综合示例</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023527.png" alt=""></p>
<h3 id="loop">LOOP</h3>
<h3 id="repeat">REPEAT</h3>
<h1 id="数据库管理">数据库管理</h1>
<h2 id="数据备份与还原">数据备份与还原</h2>
<p>MySQL提供了一个 mysqldump 命令，可以备份单个数据库、多个数据库、所有数据库</p>
<p>1）单个数据库备份</p>
<p>语法：mysqldump -uusername -ppassword dbname [tbname1 [tbname2&hellip;]] &gt; filename.sql</p>
<p>使用 mysqldump 命令备份数据库时，直接在 DOS 容器执行即可</p>
<p>2）多个数据库备份</p>
<p>语法：mysqldump -uusername -ppassword &ndash;database dbname1 [dbname2 dbname3&hellip;] &gt; filename.sql</p>
<p>3）备份所有数据库</p>
<p>语法：mysqldump -uusername -ppassword &ndash;all-database &gt; filename.sql</p>
<p>总结：在实际工作过程中，我会用界面工具，如 mysql workbench</p>
<p>如何还原数据？</p>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709023553.png" alt=""></p>
<p><code>source</code>语句，后面不能有分号</p>
<h2 id="用户管理">用户管理</h2>
<p>安装MySQL后，会自动安装一个名为 mysql 的数据库，该数据库中的表都是权限表，如 user/db/host/tables_priv/column_priv/procs_priv</p>
<p>user 表是最重要的一个权限表，MySQL中的用户分为 root 用户和普通用户</p>
<h3 id="创建普通用户">创建普通用户</h3>
<p>使用 grant 语句是创建用户最常用的方法</p>
<p>1）使用 grant 语句创建用户</p>
<p>语法：</p>
<pre><code class="language-sql">GRANT privileges ON database.table
    TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password']
    [,'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password']
</code></pre>
<p>示例：新建一个名为 user1，密码为 123，并授予该用户对chapter08.student 表有查询权限 的用户</p>
<pre><code class="language-sql">GRANT SELECT ON chapter08.student TO 'user1'@'localhost' IDENTIFIED BY '123';
</code></pre>
<p>2）使用 create user 语句创建用户</p>
<p>通常，应该先创建用户，再授予权限：</p>
<pre><code class="language-sql">CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
</code></pre>
<p>参考：https://dev.mysql.com/doc/refman/5.5/en/grant.html</p>
<p>3）使用 insert 语句创建用户</p>
<p>不管使用 CREATE USER 语句还是 GRANT 语句，在创建用户时，实际上都是在 user 表中添加一条新的记录</p>
<p>步骤一：直接在mysql.user表中插入一条记录</p>
<pre><code class="language-sql">INSERT INTO mysql.user(Host,User,Password,ss1_cipher,x509_issuer,x509_subject)
VALUES('localhost','user3',PASSWORD('123'),'','','');
</code></pre>
<p>user表其它字段都有默认值，但 ss1_cipher、x509_issuer、x509_subject 字段没有默认值，所以要设置初始值</p>
<p>步骤二：手动刷新权限表</p>
<pre><code>flush privileges;
</code></pre>
<h3 id="删除普通用户">删除普通用户</h3>
<p>方式一：使用 DROP USER 语句删除用户</p>
<pre><code class="language-sql">DROP USER 'test'@'localhost';
</code></pre>
<p>DROP USER 跟 DROP TABLE 类似</p>
<p>方式二：使用 DELETE 语句删除语句</p>
<pre><code class="language-sql">DELETE FROM mysql.user WHERE Host = 'localhost' AND User = 'test';
</code></pre>
<h3 id="修改用户密码">修改用户密码</h3>
<p>root 用户具有最高权限，不仅可以修改自己的密码，还可以修改普通用户的密码，而普通用户只能修改自己的密码</p>
<hr>
<p>修改 root 用户密码</p>
<p>1）方式一：使用 mysqladmin 命令 修改 root 用户密码</p>
<pre><code class="language-sql">mysqladmin -u username [-h hostname] -p password new_password
</code></pre>
<p>-p 后面的 password 为关键字</p>
<p>上面语句执行时，会提示输入旧密码</p>
<p>2）方式二：使用 UPDATE 语句修改 root 用户密码，此方式需要手动刷新权限表</p>
<pre><code class="language-sql">UPDATE mysql.user SET Password = PASSWORD('mypwd2') WHERE User = 'root' and Host = 'localhost'

FLUSH PRIVILEGES;
</code></pre>
<p>3）方式三：使用 SET 语句修改 root 用户的密码</p>
<pre><code>SET PASSWORD = password('123456');
</code></pre>
<p>要使用 PASSWORD()函数加密，并且新密码需要使用引号括起来</p>
<hr>
<p>root 用户修改普通用户密码</p>
<p>1）方式一：使用 GRANT 语句修改普通用户密码</p>
<p>为不影响当前用户的权限，可以使用 GRANT USAFE 语句</p>
<pre><code class="language-sql">GRANT USAFE ON *.* TO 'username'@'localhost' IDENTIFIED BY [PASSWORD] 'new_password'
</code></pre>
<p>2）方式二：使用 UPDATE 语句修改普通用户密码</p>
<p>跟root用户的相同</p>
<p>3）方式三：使用 SET 语句修改普通用户密码</p>
<p>使用 SET 语句修改普通用户密码时，需要使用 FOR 指定要修改哪个用户</p>
<pre><code class="language-sql">SET PASSWORD FOR 'username'@'hostname'=PASSWORD('new_password');
</code></pre>
<h3 id="如何找回-root-用户密码windwos">如何找回 root 用户密码？（Windwos）</h3>
<p>步骤一：在“运行”对话框中，使用 net 命令停止 MySQL 服务</p>
<pre><code>net stop mysql
</code></pre>
<p>步骤二：在“运行”对话框中，使用<code>--skip-grant-tables</code>启动MySQL服务</p>
<pre><code>--skip-grant-tables
</code></pre>
<p>步骤三：重新开启一个“运行”对话框，登录MySQL服务器</p>
<pre><code class="language-sql">mysql -u root
</code></pre>
<p>步骤四：使用 UPDATE 语句设置root用户密码</p>
<pre><code class="language-sql">
</code></pre>
<p>步骤五：加载权限表</p>
<pre><code class="language-sql">FLUSH PRIVILEGES;
</code></pre>
<h2 id="权限管理">权限管理</h2>
<p>TODO&hellip;</p>

    </div>
    <div class="article-footer">
<blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    <li class="post-copyright-link hidden-xs">
      <strong>Permalink: </strong>
      <a href="https://rulerLwx.gitee.io/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/" title="MySQL-2-基础" target="_blank" rel="external">https://rulerLwx.gitee.io/2020/07/mysql-2-%E5%9F%BA%E7%A1%80/</a>
    </li>
    <li class="post-copyright-license">
      <strong>License：</strong><a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN</a>
    </li>
  </ul>
</blockquote>

<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="https://gitee.com/rulerLwx" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="https://rulerLwx.gitee.io/avatar.png" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="https://gitee.com/rulerLwx" target="_blank"><span class="text-dark">rulerLwx</span><small class="ml-1x">thinking...</small></a></h3>
        <div>Good Good Study, Day Day Up~</div>
      </div>
    </figure>
  </div>
</div>
    </div>
  </article>
<section id="comments">
    <div id="vcomments"></div>
</section>

</div><nav class="bar bar-footer clearfix" data-stick-bottom>
    <div class="bar-inner">
        <ul class="pager pull-left">
            <li class="prev">
                <a href="https://rulerLwx.gitee.io/2020/07/mysql-3-sql%E4%BC%98%E5%8C%96/" title="MySQL-3-SQL-优化"><i
                        class="icon icon-angle-left"
                        aria-hidden="true"></i><span>&nbsp;&nbsp;Older</span></a>
            </li>
            <li class="next">
                <a href="https://rulerLwx.gitee.io/2020/07/mysql-1-%E5%9F%BA%E7%A1%80/"
                    title="MySQL-1-基础"><span>Newer&nbsp;&nbsp;</span><i
                        class="icon icon-angle-right" aria-hidden="true"></i></a>
            </li>
            
            <li class="toggle-toc">
                <a class="toggle-btn collapsed" data-toggle="collapse" href="#collapseToc" aria-expanded="false"
                    title="Catalogue" role="button">
                    <span>[&nbsp;</span><span>Catalogue</span>
                    <i class="text-collapsed icon icon-anchor"></i>
                    <i class="text-in icon icon-close"></i>
                    <span>]</span>
                </a>
            </li>
        </ul>
        <div class="bar-right">
            <div class="share-component" data-sites="weibo,qq,wechat"
                data-mobile-sites="weibo,qq,qzone"></div>
        </div>
    </div>
</nav>

</main><footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
<ul class="social-links">
    <li><a href="https://gitee.com/rulerLwx" target="_blank" title="gitee" data-toggle=tooltip data-placement=top >
            <i class="icon icon-gitee"></i></a></li>
    <li><a href="https://github.com/wolf-lea" target="_blank" title="github" data-toggle=tooltip data-placement=top >
            <i class="icon icon-github"></i></a></li>
</ul>
  <div class="copyright">
    &copy;2020  -
    2020
    <div class="publishby">
        Theme by <a href="https://github.com/xiaoheiAh" target="_blank"> xiaoheiAh </a>base on<a href="https://github.com/xiaoheiAh/hugo-theme-pure" target="_blank"> pure</a>.
    </div>
  </div>
</footer>

<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.2/MathJax.js?config=TeX-MML-AM_SVG"></script>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
            showMathMenu: false, //disables context menu
            tex2jax: {
            inlineMath: [ ['$','$'], ['\\(','\\)'] ]
           }
    });
</script>


<script src="https://cdn.jsdelivr.net/npm/jquery@3.4.1/dist/jquery.min.js"></script>
<script>
    window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/highlight.min.js"></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/python.min.js" defer></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/javascript.min.js" defer></script><script>
    hljs.configure({
        tabReplace: '    ', 
        classPrefix: ''     
        
    })
    hljs.initHighlightingOnLoad();
</script>
<script src="https://rulerLwx.gitee.io/js/application.min.bdeb64b910570b6c41badc6a05b7afb0c8ad9efd8525de3c7257d59e786326a3.js"></script>
<script src="https://rulerLwx.gitee.io/js/plugin.min.51ff8c7317566f82259170fa36e09c4493adc9b9378b427a01ad3f017ebac7dd.js"></script>

<script>
    (function (window) {
        var INSIGHT_CONFIG = {
            TRANSLATION: {
                POSTS: 'Posts',
                PAGES: 'Pages',
                CATEGORIES: 'Categories',
                TAGS: 'Tags',
                UNTITLED: '(Untitled)',
            },
            ROOT_URL: 'https:\/\/rulerLwx.gitee.io',
            CONTENT_URL: 'https:\/\/rulerLwx.gitee.io\/searchindex.json ',
        };
        window.INSIGHT_CONFIG = INSIGHT_CONFIG;
    })(window);
</script>
<script type="text/javascript" src="https://rulerLwx.gitee.io/js/insight.min.a343cd9a5a7698336b28ef3a7c16a3a1b1d2d5fb17dc8ed04022bbe08cc5459073a15bdafa3a8a58cdd56080784bdd69fa70b1ae8597565c799c57ed00f0e120.js" defer></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.min.js"></script>
<script>
    tocbot.init({
        
        tocSelector: '.js-toc',
        
        contentSelector: '.js-toc-content',
        
        headingSelector: 'h1, h2, h3',
        
        hasInnerContainers: true,
    });
</script>

<script src="https://cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/valine"></script>
<script type="text/javascript">
    var GUEST = ['nick', 'mail', 'link'];
    var meta = 'nick,mail';
    meta = meta.split(',').filter(function (item) {
        return GUEST.indexOf(item) > -1;
    });
    new Valine({
        el: '#vcomments',
        verify: null ,
        notify: null ,
        appId: 'IyAB0PSPRazTPDxitO1ddQ7O-gzGzoHsz',
        appKey: '5rBJTq4KidYF33eXwvRVhtEH',
        placeholder: 'enjoy~',
        avatar: 'mm',
        meta: meta,
        pageSize: '10' || 10,
        visitor: false 
});
</script>

  </body>
</html>
